﻿Imports System.Data
Imports System.Data.Odbc
Imports System.Text
Imports System.Collections.Generic
Imports WSEntidades
Imports Utilerias
Imports WSFactory
Imports Npgsql
Imports MySql.Data.MySqlClient

Public Class VendedoresDAL
    Private cadConex As String = ""

    Public Sub New(ByVal pCadConex As String)
        Me.cadConex = pCadConex
    End Sub
#Region "FuncionesUtilizables"

    Public Function existeVendedor(ByVal pClave As String) As Boolean
        Dim numReg As Int16 = 0
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT COUNT(*) " & _
                                            "FROM vendedores " & _
                                            "WHERE Clave = ?;"
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("Clave", pClave)
                    myConn.Open()
                    numReg = Convert.ToInt16(myComm.ExecuteScalar())
                End Using
            End Using
            Return (numReg > 0)
        Catch ex As Exception
            Throw
        Finally
            numReg = Nothing
        End Try
    End Function

    Public Function registraVendedor(ByVal pVendedor As VendedoresBE) As Boolean
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "INSERT INTO vendedores(IdEmpresa, Clave, Nombre, Porc, Status) " & _
                                            "VALUES (0, @Clave,@Nombre,@Porc,@Status);"
                    myComm.CommandType = System.Data.CommandType.Text
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("@Clave", pVendedor.Clave)
                    myComm.Parameters.AddWithValue("@Nombre", pVendedor.Nombre)
                    myComm.Parameters.AddWithValue("@Porc", pVendedor.PorcComision)
                    myComm.Parameters.AddWithValue("@Status", "A")
                    myConn.Open()
                    If (myComm.ExecuteNonQuery() = 1) Then
                        Return True
                    Else
                        Return False
                    End If
                End Using
            End Using
        Catch ex As Exception
            Throw
        End Try
    End Function

    Public Function actualizaVendedor(ByVal pVendedor As VendedoresBE, ByRef msg As String) As Boolean
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT count(*) " &
                                            "FROM vendedores " & _
                                            "WHERE IdVendedor <> @id " &
                                                "AND  Clave = @cve;"
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("@id", pVendedor.IdVendedor)
                    myComm.Parameters.AddWithValue("@cve", pVendedor.Clave)
                    myConn.Open()
                    If (Convert.ToInt16(myComm.ExecuteScalar()) > 0) Then
                        msg &= "Existe otro registro con la Clave especificada." & vbCrLf
                        Return False
                    End If

                    myComm.CommandText = "UPDATE vendedores " & _
                                            "SET Clave = @Clave, " & _
                                                "Nombre = @Nombre, " & _
                                                "Porc = @Porc, " & _
                                                "Status = @Status " & _
                                            "WHERE IdVendedor = @IdVendedor;"
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("@Clave", pVendedor.Clave)
                    myComm.Parameters.AddWithValue("@Nombre", pVendedor.Nombre)
                    myComm.Parameters.AddWithValue("@Porc", pVendedor.PorcComision)
                    Select Case pVendedor.Status
                        Case VendedoresBE.EstatusVendedor.Activo
                            myComm.Parameters.AddWithValue("@Status", "A")
                        Case VendedoresBE.EstatusVendedor.Inactivo
                            myComm.Parameters.AddWithValue("@Status", "I")
                        Case Else
                            myComm.Parameters.AddWithValue("@Status", "A")
                    End Select
                    myComm.Parameters.AddWithValue("@IdVendedor", pVendedor.IdVendedor)
                    If (myComm.ExecuteNonQuery() = 1) Then
                        Return True
                    Else
                        Return False
                    End If
                End Using
            End Using
        Catch ex As Exception
            Throw
        End Try
    End Function

    Public Function getVendedorByClave(ByVal pClave As String) As VendedoresBE
        Dim tmpVendedor As VendedoresBE
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT Clave, IdVendedor, Nombre, Porc, Status " & _
                                            "FROM vendedores " & _
                                            "WHERE Clave = @Clave;"
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.AddWithValue("@Clave", pClave)
                    myConn.Open()
                    Using myDR As MySqlDataReader = myComm.ExecuteReader()
                        If (myDR.HasRows()) Then
                            myDR.Read()
                            tmpVendedor = VendedoresFact.Build(myDR)
                        Else
                            tmpVendedor = Nothing
                        End If
                    End Using
                End Using
            End Using
            Return tmpVendedor

        Catch ex As Exception
            Throw
        Finally
            tmpVendedor = Nothing
        End Try
    End Function

    Public Function getVendedorById(ByVal pId As Integer) As VendedoresBE
        Dim tmpVendedor As VendedoresBE
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT Clave, IdVendedor, Nombre, Porc, Status " & _
                                            "FROM vendedores " & _
                                            "WHERE IdVendedor = @id;"
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.AddWithValue("@id", pId)
                    myConn.Open()
                    Using myDR As MySqlDataReader = myComm.ExecuteReader()
                        If (myDR.HasRows()) Then
                            myDR.Read()
                            tmpVendedor = VendedoresFact.Build(myDR)
                        Else
                            tmpVendedor = Nothing
                        End If
                    End Using
                End Using
            End Using
            Return tmpVendedor

        Catch ex As Exception
            Throw
        Finally
            tmpVendedor = Nothing
        End Try
    End Function

    Public Function getlistadoVendedores(ByVal pNombre As String, ByVal tipoEstatus As Enumeraciones.estatusParaBusquedas,
                                         ByVal pRegistroInicial As Integer, ByVal pPageSize As Integer,
                                         ByVal pSortColumn As Enumeraciones.campoOrdenamiento, ByVal pSortDirection As Enumeraciones.tipoOrdenamiento,
                                         ByRef pTotalRegistros As Integer) As List(Of VendedoresBE)
        pTotalRegistros = 0
        Dim VendedoresLs As List(Of VendedoresBE) = New List(Of VendedoresBE)
        Dim qryEstatus As String = ""
        Select Case tipoEstatus
            Case Enumeraciones.estatusParaBusquedas.Todos
                'No aplica filtro
            Case Enumeraciones.estatusParaBusquedas.Activos
                qryEstatus = " AND Status = 'A' "
            Case Enumeraciones.estatusParaBusquedas.Inactivos
                qryEstatus = " AND Status = 'I' "
        End Select
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT SQL_CALC_FOUND_ROWS Clave, IdVendedor, Nombre, Porc, Status " & _
                                           "FROM vendedores " & _
                                           "WHERE Nombre LIKE @Nombre " &
                                                qryEstatus &
                                           Enumeraciones.getOrderByStatement(Enumeraciones.catalogo.Vendedores, pSortColumn, pSortDirection) &
                                           " LIMIT @regInic, @pageSize;"
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.AddWithValue("@Nombre", "%" & pNombre & "%")
                    myComm.Parameters.AddWithValue("@regInic", pRegistroInicial)
                    myComm.Parameters.AddWithValue("@pageSize", pPageSize)
                    myConn.Open()
                    Using myDR As MySqlDataReader = myComm.ExecuteReader()
                        While myDR.Read()
                            Dim Vendedores As VendedoresBE = VendedoresFact.Build(myDR)
                            If (Not Vendedores Is Nothing) Then
                                VendedoresLs.Add(Vendedores)
                            End If
                            Vendedores = Nothing
                        End While
                    End Using
                    Using myComm2 As MySqlCommand = myConn.CreateCommand()
                        myComm2.CommandType = CommandType.Text
                        myComm2.CommandText = "SELECT FOUND_ROWS();"
                        pTotalRegistros = Convert.ToInt32(myComm2.ExecuteScalar())
                    End Using
                End Using
            End Using
            Return VendedoresLs
        Catch ex As Exception
            Throw
        Finally
            VendedoresLs = Nothing
            qryEstatus = Nothing
        End Try
    End Function

    Public Function actualizaEstatusVendedor(ByVal pid As Integer, ByVal pSetActivo As Boolean) As Boolean
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "UPDATE vendedores " & _
                                            "SET Status = @Status " & _
                                            "WHERE IdVendedor = @id; "
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.Clear()
                    If (pSetActivo) Then
                        myComm.Parameters.AddWithValue("@Status", "A")
                    Else
                        myComm.Parameters.AddWithValue("@Status", "I")
                    End If
                    myComm.Parameters.AddWithValue("@id", pid)
                    myConn.Open()
                    If (myComm.ExecuteNonQuery() = 1) Then
                        Return True
                    Else
                        Return False
                    End If
                End Using
            End Using
        Catch ex As Exception
            Throw
        End Try
    End Function
#End Region

    

End Class
